/************************************************************************************/
/*  Program name CAPFNDIMP_BG_MAPPING.sql                                           */
/*  Creater:  WONG Wing Kin (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 30 NOV, 2005   WONG Wing Kin         First created                               */
/************************************************************************************/

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE

	lv_Prog_ID		VARCHAR2(100) 	:=	'CAPFNDIMP_BG_MAPPING';

	lv_Scc_Count	NUMBER			:=	0;
	lv_Err_Count	NUMBER			:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	lv_NGO			VARCHAR2(100);
	lv_NGO_Count	NUMBER;

	lv_BG_Old_ID	NUMBER;
	lv_BG_New_ID	NUMBER;

BEGIN
	lv_NGO			:=	UPPER('&NGO_Code');

	SELECT	COUNT('A')
	INTO	lv_NGO_Count
	FROM	HR_ALL_ORGANIZATION_UNITS
	WHERE	SUBSTR(Name,4,4)	=	lv_NGO;

	IF	lv_NGO_Count	>=	1	THEN

		SELECT	Business_Group_ID
		INTO	lv_BG_Old_ID
		FROM	PER_BUSINESS_GROUPS
		WHERE	Name	=	CAPBRIMP_MAPPING.RenameStr(lv_NGO,'BG_'||lv_NGO);

		SELECT	Business_Group_ID
		INTO	lv_BG_New_ID
		FROM	PER_BUSINESS_GROUPS
		--WHERE	Name	=	'BG_'||lv_NGO;
		WHERE	Name	=	'BG_'||lv_NGO; 
		
		DELETE	FROM	CRST_FND_BG_MAP;

		INSERT	INTO	CRST_FND_BG_MAP
		VALUES
		(
		lv_BG_Old_ID,
		lv_BG_New_ID
		);

		INSERT INTO CAPCNV_LOG(
			PROG_ID,
			PROCESS_DATE,
			RECORD_PROCESSED,
			RECORD_REJECTED,
			RECORD_EXTRACTED,
			ERR_MSG
		) VALUES (
			lv_Prog_ID,
			SYSDATE,
			lv_Scc_Count,
			lv_Err_Count,
			lv_Scc_Count-lv_Err_Count,
			lv_Err_Msg
		);
	ELSE
		lv_Err_Count	:=	lv_Err_Count + 1;
		lv_Err_Msg		:=	lv_NGO||': No such NGO exists';
		INSERT INTO CAPCNV_LOG(
			PROG_ID,
			PROCESS_DATE,
			RECORD_PROCESSED,
			RECORD_REJECTED,
			RECORD_EXTRACTED,
			ERR_MSG
		) VALUES (
			lv_Prog_ID,
			SYSDATE,
			lv_Scc_Count,
			lv_Err_Count,
			lv_Scc_Count-lv_Err_Count,
			lv_Err_Msg
		);
	END IF;

	COMMIT;
END;
/